import json

from django.http.response import JsonResponse
from django.views.decorators.http import require_http_methods

import sys

from utils import custom_utils, http_response

sys.path.insert(0, '..')
from mysite import db_config


@require_http_methods(['GET'])
def quality_detail(request):
    """
    查询给定日期的检核明细结果，返回最新版本数据
    """
    company = request.GET.get('company')
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')
    
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        sql = f"""select id,
				source_system,
				check_item,
				target_table,
				risk_market_item,
				problem_type,
				check_sql,
				item_count,
				problem_count,
				concat(round(problem_count*100/item_count,2),'%'),
				note,
				check_date,
				check_version
				from check_result_{company} a ,(select company,
				 max(version) version from  check_result_version
				where company = '{company}'
				and year(check_date)={year}
                and quarter(check_date)={quarter}
                and month(check_date)={month}
                and day(check_date)={day}
				) b
				where a.risk_market_item='是'
				and a.company = b.company
				and a.check_version = b.version
				and year(check_date)={year}
                and quarter(check_date)={quarter}
                and month(check_date)={month}
                and day(check_date)={day}
				order by check_version ,id asc"""
        curs.execute(sql)
        result = curs.fetchall()
        
        result_list = []
        for i in result:
            result_dict = {"id": i[0], "source_system": i[1], "check_item": i[2], "target_table": i[3], "risk_market_item": i[4],
                           "problem_type": i[5], "check_sql": i[6], "item_count": i[7], "problem_count": i[8], "problem_per": i[9],
                           "note": i[10], "check_date": i[11], "check_version":  i[12]}
            result_list.append(result_dict)
        return JsonResponse({'data': result_list})
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()
        
        
@require_http_methods(['GET'])
def report_detail(request):
    """
    查询给定日期，各公司的检核明细结果，返回最新版本数据
    """
    company = request.GET.get('company')
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')
    
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        sql  = f"""select a.check_item,a.problem_type,a.problem_count,a.item_count,concat(a.problem_per,'%') problem_per 
                    from check_result_{company} a,
                    (
                        select max(a.check_version) check_version
                        from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
                        and b.year={year}
                        and b.quarter={quarter}
                        and b.month={month}
                        and b.day={day}
                    ) b
                    where a.risk_market_item='是'
                    and a.check_version=b.check_version
                    and (a.problem_count<>0 or a.problem_count is null)
                    order by a.problem_type,a.problem_count desc"""
        curs.execute(sql)
        result = curs.fetchall()
        
        result_list = []
        for i in result:
            result_dict = {"check_item": i[0],
                           "problem_type": i[1],
                           "problem_count": i[2],
                           "item_count": i[3],
                           "problem_per": i[4],
                           }
            result_list.append(result_dict)
        return JsonResponse({'data': result_list})
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def model_page_version(request):
    """根据日期返回数据期相关信息
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT
                        a.company,
                        b.menu_name,
                        a.version,
                        DATE_FORMAT(a.check_date,'%Y%m%d') check_date_str,
                        a.check_date
                    FROM
                        check_result_version a
                        LEFT JOIN hbd_menu b ON a.company = b.menu_name_en
                        where a.company = '{company}'
                        and a.check_date >= '{startTime}' and a.check_date < date_add('{endTime}', interval 1 day)
                        order by a.version desc
                        
                """.format(company=param_dict.get('company'),
                           startTime=param_dict.get('startTime'),
                           endTime=param_dict.get('endTime'))
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_page_summary(request):
    """ 规则汇总
    参数  company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT
                cc.id,
                cc.check_item,
                cc.target_table,
                cc.risk_market_item,
                cc.problem_type,
                cc.check_version,
                cc.note,
                cc.check_log,
                max( check_date ) check_date,
                sum(problem_count) problem_count,
                sum(total_count)  total_count,
                round( sum( detail_coefficient )/ count( 1 ), 2 ) total_coefficient
            FROM
                (
                SELECT
                    a.id,
                    a.note,
                    a.check_log,
                    d.id detail_id,
                    d.t_name,
                    d.c_name,
                    c.NAME check_item,
                    target_table,
                    risk_market_item,
                    b.cmc problem_type,
                    d.result problem_count,
                    e.result total_count,
                    round( (e.result-d.result)  * 100 / e.result, 4 ) detail_coefficient,
                    check_date,
                    a.check_version 
                FROM
                    check_result_{company} a
                    LEFT JOIN data_standard_desc c ON a.check_item = c.index_id
                    LEFT JOIN hbd_aa10 b ON a.problem_type = b.dm 
                    AND b.category = 'quality'
                    LEFT JOIN check_result_detail_{company} d ON a.id = d.pid 
                    AND d.check_version = a.check_version
                    LEFT JOIN check_result_detail_{company} e ON a.id = e.pid 
                    AND d.denominator_order = e.category_order 
                    AND e.check_version = d.check_version 
                WHERE
                    a.risk_market_item = '是' 
                    AND a.STATUS = '已启用' 
                    AND a.check_version = {check_version} 
                    AND d.type = 'numerator' 
                    AND e.type = 'denominator' 
                ) cc 
            GROUP BY
                cc.id,
                cc.check_item,
                cc.target_table,
                cc.risk_market_item,
                cc.problem_type,
                cc.check_version,
                cc.note,
                check_log
                order by id 
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version'))
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_page_detail(request):
    """ 规则明细
    参数  company  check_version pid (汇总行的id)
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT
                    a.id,
                    a.note,
                    d.id detail_id,
                    d.t_name,
                    d.c_name,
                    c.NAME check_item,
                    target_table,
                    risk_market_item,
                    b.cmc problem_type,
                    coalesce(d.result,'nan') problem_count,
                    coalesce(e.result,'nan') total_count,
                    round( d.result * 100 / e.result, 4 ) detail_coefficient,
                    check_date,
                    a.check_version ,
                    coalesce(d.check_log,e.check_log) check_log
                FROM
                    check_result_{company} a
                    LEFT JOIN data_standard_desc c ON a.check_item = c.index_id
                    LEFT JOIN hbd_aa10 b ON a.problem_type = b.dm 
                    AND b.category = 'quality'
                    LEFT JOIN check_result_detail_{company} d ON a.id = d.pid 
                    AND d.check_version = a.check_version
                    LEFT JOIN check_result_detail_{company} e ON a.id = e.pid 
                    AND d.denominator_order = e.category_order 
                    AND e.check_version = d.check_version 
                WHERE
                    a.risk_market_item = '是' 
                    AND a.STATUS = '已启用' 
                    AND a.check_version = {check_version} 
                    AND d.type = 'numerator' 
                    AND e.type = 'denominator' 
                    and a.id = {pid}
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version'),
                           pid=param_dict.get('pid'))
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


# 数据趋势分析对应的页面接口
@require_http_methods(['POST'])
def model_history_trend(request):
    """# 趋势分析
    参数  company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT
                   aaa.check_version,
                    aaa.result,
                    aaa.result_err,
                    concat( round( aaa.result_err * 100 / aaa.result, 2 ), '%' ) bl,
                    aaa.total_coefficient,
                    bbb.check_version check_version_pre,
                    bbb.result result_pre,
                    bbb.result_err result_err_pre,
                    concat( round( bbb.result_err * 100 / bbb.result, 2 ), '%' ) bl_pre ,
                    bbb.total_coefficient total_coefficient_pre,
                    case when bbb.total_coefficient is null then '--'
                    else concat(round((aaa.total_coefficient-bbb.total_coefficient)*100 /bbb.total_coefficient,2),'%')
                    end  coefficient_tb
                FROM
                    (
                    select aaa2.*,( @rownum := @rownum + 1 ) AS rownum from 
										(select check_version,
                    sum(result_err) result_err,
                    sum(result) result,
                    round((1-avg(total_coefficient))*100,2) total_coefficient
                    from (
                    SELECT
                        id,
                        check_version,
                        sum( result_err ) result_err,
                        sum( result ) result ,
                        sum(total_coefficient)/count(1) total_coefficient
                    FROM
                        (
                        SELECT
                            a.id,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.result result_err,
                            c.result ,
                            b.result/c.result  total_coefficient
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid 
                        WHERE
                            a.check_version <= {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                        ) aa 
                    GROUP BY
                        id,check_version 
                    ) aaa1
                    group by check_version
										order by check_version desc
										) aaa2,( SELECT @rownum := 0 ) AS t	
                    )aaa
                    LEFT JOIN (
                     select aaa2.*,( @rownum1 := @rownum1 + 1 ) AS rownum from 
										(select check_version,
                    sum(result_err) result_err,
                    sum(result) result,
                    round((1-avg(total_coefficient))*100,2) total_coefficient
                    from (
                    SELECT
                        id,
                        check_version,
                        sum( result_err ) result_err,
                        sum( result ) result ,
                        sum(total_coefficient)/count(1) total_coefficient
                    FROM
                        (
                        SELECT
                            a.id,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.result result_err,
                            c.result ,
                            b.result/c.result  total_coefficient
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid 
                        WHERE
                            a.check_version <= {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                        ) aa 
                    GROUP BY
                        id,check_version 
                    ) aaa1
                    group by check_version
										order by check_version desc 
										) aaa2,( SELECT @rownum1 := 0 ) AS tt	
                    
                    ) bbb ON aaa.rownum=bbb.rownum-1
                    limit 12
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version')
                           )
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_rule_error(request):
    """# 各种规则错误数据量占比
    参数  company  check_version pid (汇总行的id)
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT
                        check_version,
                        problem_type,
                        sum( result_err ) result_err,
                        sum( result ) result 
                    FROM
                        (
                        SELECT
                            a.id,
                            aa10.cmc problem_type,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.result result_err,
                            c.result 
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid 
                            left join hbd_aa10 aa10 on aa10.dm = a.problem_type and aa10.category='quality'
                        WHERE
                            a.check_version = {check_version} 
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                        ) aa 
                    GROUP BY
                    check_version ,problem_type
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version')
                           )
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_history_rule_detail(request):
    """# 各规则表 错误数据明细分析
    参数  company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT a.id,
                        a.note,
                        a.target_table,
                        aa10.cmc problem_type,
                        a.check_version,
                        b.id d_id,
                        b.type,
                        b.t_name,
                        b.c_name,
                        b.result result_err,
                        c.result 
                    FROM
                        check_result_{company} a
                        JOIN check_result_detail_{company} b ON a.id = b.pid 
                        AND a.check_version = b.check_version
                        LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                        AND b.denominator_order = c.category_order 
                        AND c.pid = b.pid
                        LEFT JOIN hbd_aa10 aa10 ON aa10.dm = a.problem_type 
                        AND aa10.category = 'quality' 
                    WHERE
                        a.check_version = {check_version}
                        AND a.result_set IS NOT NULL 
                        AND b.type = 'numerator' 
                        AND c.type = 'denominator' 
                        order by id,d_id
	
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version')
                           )
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_target_table_detail(request):
    """# 各规则表 错误数据占比
    参数  company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """select 
                        target_table,
                        sum(result_err) result_err
                        from (
                        SELECT
                            a.id,
                            a.note,
                            a.target_table,
                            aa10.cmc problem_type,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.t_name,
                            b.c_name,
                            b.result result_err,
                            c.result 
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid
                            LEFT JOIN hbd_aa10 aa10 ON aa10.dm = a.problem_type 
                            AND aa10.category = 'quality' 
                        WHERE
                            a.check_version = {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                            order by id,d_id
                            ) ccc
                            group by target_table
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version')
                           )
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


# 数据质量检查报告页面接口
@require_http_methods(['POST'])
def model_comprehensive_score(request):
    """# 报告中的趋势数据  和趋势分析中的柱状图sql一致
        1 综合评分 2 及错误情况  参数  company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """SELECT
                   aaa.check_version,
                    aaa.result,
                    aaa.result_err,
                    round(aaa.result_err / aaa.result, 4 ) bl,
                    aaa.total_coefficient,
                    bbb.check_version check_version_pre,
                    bbb.result result_pre,
                    bbb.result_err result_err_pre,
                    round( bbb.result_err / bbb.result, 4 ) bl_pre ,
                    bbb.total_coefficient total_coefficient_pre,
                    case when bbb.total_coefficient is null then '--'
                    else round((aaa.total_coefficient-bbb.total_coefficient) /bbb.total_coefficient,4)
                    end  coefficient_tb
                FROM
                    (
                    select aaa2.*,( @rownum := @rownum + 1 ) AS rownum from 
			       (select check_version,
                    sum(result_err) result_err,
                    sum(result) result,
                    round((1-avg(total_coefficient))*100,2) total_coefficient
                    from (
                    SELECT
                        id,
                        check_version,
                        sum( result_err ) result_err,
                        sum( result ) result ,
                        sum(total_coefficient)/count(1) total_coefficient
                    FROM
                        (
                        SELECT
                            a.id,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.result result_err,
                            c.result ,
                            b.result/c.result  total_coefficient
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid 
                        WHERE
                            a.check_version <= {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                        ) aa 
                    GROUP BY
                        id,check_version 
                    ) aaa1
                    group by check_version
					order by check_version desc
					) aaa2,( SELECT @rownum := 0 ) AS t	
                    )aaa
                    LEFT JOIN (
                     select aaa2.*,( @rownum1 := @rownum1 + 1 ) AS rownum from 
										(select check_version,
                    sum(result_err) result_err,
                    sum(result) result,
                    round((1-avg(total_coefficient))*100,2) total_coefficient
                    from (
                    SELECT
                        id,
                        check_version,
                        sum( result_err ) result_err,
                        sum( result ) result ,
                        sum(total_coefficient)/count(1) total_coefficient
                    FROM
                        (
                        SELECT
                            a.id,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.result result_err,
                            c.result ,
                            b.result/c.result  total_coefficient
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid 
                        WHERE
                            a.check_version <= {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                        ) aa 
                    GROUP BY
                        id,check_version 
                    ) aaa1
                    group by check_version
					order by check_version desc 
					) aaa2,( SELECT @rownum1 := 0 ) AS tt	
                    
                    ) bbb ON aaa.rownum=bbb.rownum-1
                    limit 12
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version')
                           )
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_quality_desc(request):
    """# 报告中的趋势数据  和趋势分析中的柱状图sql一致
        3 规则数据情况  参数  company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = """select 
                        'TOTAL_AGG' dm,
						'总计' problem_type,
                        sum(result_err) result_err,
						sum(result) result,
						round(sum(result_err)/sum(result),4) err_per
                        from (
                        SELECT
                            a.id,
                            a.note,
                            a.target_table,
                            aa10.cmc problem_type,
							aa10.dm dm,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.t_name,
                            b.c_name,
                            b.result result_err,
                            c.result 
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid
                            LEFT JOIN hbd_aa10 aa10 ON aa10.dm = a.problem_type 
                            AND aa10.category = 'quality' 
                        WHERE
                            a.check_version = {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                            ) ccc
                    union all
                    select 
                        dm,
						problem_type,
                        sum(result_err) result_err,
						sum(result),
						round(sum(result_err)/sum(result),2) err_bl
                        from (
                        SELECT
                            a.id,
                            a.note,
                            a.target_table,
                            aa10.cmc problem_type,
							aa10.dm dm,
                            a.check_version,
                            b.id d_id,
                            b.type,
                            b.t_name,
                            b.c_name,
                            b.result result_err,
                            c.result 
                        FROM
                            check_result_{company} a
                            JOIN check_result_detail_{company} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{company} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid
                            LEFT JOIN hbd_aa10 aa10 ON aa10.dm = a.problem_type 
                            AND aa10.category = 'quality' 
                        WHERE
                            a.check_version = {check_version}
                            AND a.result_set IS NOT NULL 
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                            ) ccc
                            group by dm,problem_type
                """.format(company=param_dict.get('company'),
                           check_version=param_dict.get('check_version')
                           )
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)
